package jsyf.oa.dao.weekplan;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.weekplan.WeekPlanEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * Created by LT on 2018/3/19.
 */
@Repository
public class WeekPlanDao extends BaseDao {

    public WeekPlanDao() {
        super.iBaseEntity = new WeekPlanEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, WeekPlanEntity.class);
    }

    //模糊查询客户信息
    public List<JSONObject> getNameAndTel(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" t_wrt_customer.ID AS customerId, \n");
        sql.append(" t_wrt_customer.NAME AS customerName, \n");
        sql.append(" t_wrt_customer.TEL AS customerTel, \n");
        sql.append(" t_wrt_customer.LAST_VISIT_TIME AS lastVisitTime, \n");
        sql.append(" t_wrt_customer.PROVINCE_NAME AS provinceName, \n");
        sql.append(" t_wrt_customer.CITY_NAME AS cityName, \n");
        sql.append(" t_wrt_customer.COUNTY_NAME AS countyName, \n");
        sql.append(" t_wrt_customer.OTHER_CONDITION AS otherCondition, \n");
        sql.append(" t_wrt_customer.BUY_TYPE AS buyType, \n");
        sql.append(" t_wrt_customer.CUSTOMER_CREDIT AS customerCredit, \n");
        sql.append(" t_wrt_customer.COMPETITOR_CONDITION AS competitorCondition, \n");
        sql.append(" t_wrt_customer.INTENTION_MODEL AS intentionModel, \n");
        sql.append(" t_wrt_customer.CUSTOMER_INDUSTRY AS customerIndustry, \n");
        sql.append(" t_wrt_customer.EXISTING_DEVICE AS existingDevice, \n");
        sql.append(" t_wrt_customer.CUSTOMER_ADDRESS AS buyerAddress, \n");
        sql.append(" t_wrt_customer.ID_CARD AS idCard, \n");
        sql.append(" concat_ws('-',\n");
        sql.append(" t_wrt_customer.CITY_NAME, \n");
        sql.append(" t_wrt_customer.COUNTY_NAME \n");
        sql.append(") AS customerAddress \n");
        sql.append(" FROM \n");
        sql.append(" T_WRT_CUSTOMER\n");
        sql.append(" WHERE 1=1 \n");
        sql.append(condition);
        sql.append(" LIMIT " + 0 + "," + 10);
        List<JSONObject> listResult = super.queryForJsonList(sql.toString(), params);
        return listResult;
    }

    public List<JSONObject> getJSONListAll(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" v1.name AS createUserName ,");
        sql.append(" t.ID AS id ,");
        sql.append(" CUSTOMER_NAME AS customerName ,");
        sql.append(" LAST_VISIT_TIME AS lastVisitTime ,");
        sql.append(" CUSTOMER_ADDRESS AS customerAddress ,");
        sql.append(" INTENTION_DEVICE AS intentionDevice ,");
        sql.append(" REMAKE AS remake ,");
        sql.append(" CREATE_USER_ID AS createUserId ,");
        sql.append(" CREATE_TIME AS createTime ,");
        sql.append(" WEEK_TIME AS weekTime ,");
        sql.append(" CUSTOMER_INDUSTRY AS customerIndustry ,");
        sql.append(" WEEK_NAME AS weekName ");
        sql.append(" FROM ");
        sql.append(" t_wrt_week_plan t ");
        sql.append(" LEFT JOIN v_sys_user v1 ON t.CREATE_USER_ID = v1.id ");
        sql.append(" LEFT JOIN v_sys_user v2 ON v1.parentId = v2.id");
        sql.append(" WHERE 1=1");
        sql.append(condition);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), params);
        return jsonList;
    }

    public List<JSONObject> getTotal(String lastSun, String lastSat) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<Object>();
        sql.append(" SELECT");
        sql.append(" companyName,");
        sql.append(" IFNULL(count(1),0) AS count");
        sql.append(" FROM");
        sql.append(" t_wrt_week_plan t");
        sql.append(" LEFT JOIN v_sys_user ON t.CREATE_USER_ID = v_sys_user.id ");
        sql.append(" WHERE");
        sql.append(" LEFT (WEEK_TIME, 10) BETWEEN ? and ?");
        sqlArgs.add(lastSun);
        sqlArgs.add(lastSat);
        sql.append(" AND t.CUSTOMER_NAME IS NOT NULL");
        sql.append(" GROUP BY v_sys_user.companyName");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), sqlArgs.toArray());
        return jsonList;
    }

    //所有大客户列表大客户支持看到的页面
    public List<JSONObject> getCompanyList(String roleName, String userId, String lastSun, String lastSat) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<Object>();
        sql.append(" SELECT");
        sql.append(" companyId,");
        sql.append(" companyName,");
        sql.append(" count(1) as total");
        sql.append(" FROM");
        sql.append(" v_sys_user ");
        sql.append(" WHERE");
        sql.append(" (companyName LIKE '%大客户' AND roleName = '大客户经理' AND jobState!=1");
        sql.append(" )");
        sql.append(" or (departName='大客户部' and jobState!=1  AND roleName = '大客户经理'");
        sql.append(" )");
        sql.append(" AND companyName!=\"山西沃源\"");
        sql.append(" GROUP BY");
        sql.append(" companyName");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), sqlArgs.toArray());
        return jsonList;
    }

    //大客户总监看到的页面
    public List<JSONObject> getSomeCompanyList(String UserId, String roleName, String companyId, String lastSun, String lastSat) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<Object>();
        sql.append(" SELECT");
        sql.append(" companyId,");
        sql.append(" companyName,");
        sql.append(" count(1) as total");
        sql.append(" FROM");
        sql.append(" v_sys_user ");
        sql.append(" WHERE");
        sql.append(" (companyName LIKE '%大客户' AND roleName = '大客户经理' AND jobState!=1");
        if ("大客户总监".equals(roleName) || "大客户总经理".equals(roleName) || "销售总监".equals(roleName) || "总经理".equals(roleName)) {
            sql.append(" AND companyId in (select companyId from v_sys_user where id=?)");
            sqlArgs.add(UserId);
        }
        sql.append(" )");
        sql.append(" or (departName='大客户部' and jobState!=1  AND roleName = '大客户经理'");
        if ("大客户总监".equals(roleName) || "大客户总经理".equals(roleName) || "销售总监".equals(roleName) || "总经理".equals(roleName)) {
            sql.append(" AND companyId in (select companyId from v_sys_user where id=?)");
            sqlArgs.add(UserId);
        }
        sql.append(" )");
        sql.append(" AND companyName!=\"山西沃源\"");
        sql.append(" GROUP BY");
        sql.append(" companyName");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), sqlArgs.toArray());
        return jsonList;
    }

    public List<JSONObject> getDetail(String userId, String companyId, String lastSun, String lastSat) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<Object>();
        sql.append(" SELECT ");
        sql.append(" v1.name AS createUserName ,");
        sql.append(" t.ID AS id ,");
        sql.append(" CUSTOMER_NAME AS customerName ,");
        sql.append(" LAST_VISIT_TIME AS lastVisitTime ,");
        sql.append(" CUSTOMER_ADDRESS AS customerAddress ,");
        sql.append(" INTENTION_DEVICE AS intentionDevice ,");
        sql.append(" REMAKE AS remake ,");
        sql.append(" CREATE_USER_ID AS createUserId ,");
        sql.append(" CREATE_TIME AS createTime ,");
        sql.append(" WEEK_TIME AS weekTime ,");
        sql.append(" CUSTOMER_INDUSTRY AS customerIndustry ,");
        sql.append(" WEEK_NAME AS weekName ");
        sql.append(" FROM ");
        sql.append(" t_wrt_week_plan t ");
        sql.append(" LEFT JOIN v_sys_user v1 ON t.CREATE_USER_ID = v1.id ");
        sql.append(" WHERE CREATE_USER_ID IN (");
        sql.append(" SELECT USER_ID FROM");
        sql.append(" t_sys_user_2_region");
        sql.append(" WHERE");
        sql.append(" REGION_ID IN (");
        sql.append(" SELECT");
        sql.append(" REGION_ID");
        sql.append(" FROM");
        sql.append(" t_sys_user_2_region");
        sql.append(" WHERE USER_ID=?)");
        sql.append(" )");
        sql.append(" and companyId=?");
        sql.append(" AND LEFT(WEEK_TIME,10) BETWEEN ? AND ?\n");
        sql.append(" ORDER BY createUserId,WEEK_TIME");
        sqlArgs.add(userId);
        sqlArgs.add(companyId);
        sqlArgs.add(lastSun);
        sqlArgs.add(lastSat);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), sqlArgs.toArray());
        return jsonList;
    }
}
